package com.simple.elpmobile.onlinecase.engine.manager;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;


import com.iflytek.elpmobile.utils.Logger;
import com.iflytek.elpmobile.utils.StringUtils;
import com.simple.elpmobile.onlinecase.db.DataChangedListener;
import com.simple.elpmobile.onlinecase.db.TableManager;
import com.simple.elpmobile.onlinecase.engine.IManager;
/**
 * 
 * @author jgzheng
 * @descripton  
 * * 用于管理数据�
 * 1、管理数据库创建、升�
 * 2、表格数据的增删改查
 */
public class DBManager implements IManager
{
	public final static String TAG = "DBManager";
	
	private Context mContext;
	
    public static final int DATA_DEL = 0;
    public static final int DATA_ADD = 1;
    public static final int DATA_CHANGED = 2;
	

    /**
     * 数据库名
     */
    private static final String DB_NAME = "database";
    
    /**
     * 数据库帮助类实例
     */
    private static DBManager instance = null;

    /**
     * 表格管理�
     */
    private HashMap<String, TableManager> tableManagers = null;

    /**
	 * android数据库帮助对�
	 */
    private OpenHelper openHelper;
    
    private HashMap<String, DataChangedListener> listeners;

    private int mOldVersion;

    private int mNewVersion;
    
    private static int tempIndex = 0;

    /**
     * 1.0版本
     */
    public static final int VER_1_0 = 10;
    
    /**
     * 当前使用的数据库版本�
     */
    public static final int VER_CURRENT = VER_1_0;
    
    
	public DBManager(Context context)
	{
		mContext = context;
		
		listeners = new HashMap<String, DataChangedListener>();
	    tableManagers = new HashMap<String, TableManager>();
	    
	    //TODO add tablemanager to tablemanagers

/*      DownloadTableManager downloadTableManager = new DownloadTableManager(this);
        this.addTableManager(DownloadTableManager.TABLE_NAME, downloadTableManager);
*/
        
        
        this.getDBHelper();
	}
	
    /**
     * 
     * 获取 数据库帮助类 的实例
     * <功能详细描述>
     * @param context 上下�
     * @return 数据库帮助类 的实�
     */
    public static DBManager getInstance(Context context)
    {
        if (instance == null)
        {
            instance = new DBManager(context);
        }
        return instance;
    }
    
    private void addTableManager(String table, TableManager tableManager)
    {
        if (table != null && tableManager != null)
        {
            Logger.d(TAG, "addTableManager, table = " + table);
            tableManagers.put(table, tableManager);
        }
        return;
    }
    
    /**
     * 为表添加一个数据修改侦听接�
     * 
     * @param table 表格�
     * @param listener 数据监听�
     */
    public void addTableListener(String table, DataChangedListener listener)
    {
        if (table != null && listener != null)
        {
            listeners.put(table, listener);
        }
    }
    
    /**
     * 表格数据更改通知
     * @param table  表格�
     * @param type 更改类型
     */
    private void fireDataChangedListener(String table, int type)
    {
        DataChangedListener listener = listeners.get(table);
        if (listener != null)
        {
            listener.onDataChanged(table);
        }
    }
    
    /**
     * 创建数据�
     * 
     * @param dbName 数据库名
     */
    public void openConnection(String dbName)
    {
        try
        {
            Logger.d(TAG, "openConnection");
            openHelper = new OpenHelper(mContext, dbName, null, VER_CURRENT);
        }
        catch (Exception e)
        {
            openConnection(getTempDatabase());
        }
    }
    
    /**
     * 获取备份表名�
     * 
     * @return
     */
    private String getTempDatabase()
    {
        return DB_NAME + (tempIndex++);
    }
    
    /**
     * 获得当前链接的数据库,java层数据库移到SD�
     * 
     * @return
     */
    private SQLiteDatabase getDBHelper()
    {
        //Logger.d(TAG, "getDBHelper");
        if (openHelper == null)
        {
            openConnection(DB_NAME);
        }
        try
        {
        	return openHelper.getWritableDatabase();
        }
        catch(Exception e)
        {
        	e.printStackTrace();
        	return null;
        }
    }
	

    /**
     * 直接执行sql语句
     * @param sql       sql语句
     */
    public void execSQL(String sql)
    {
        try
        {
            getDBHelper().execSQL(sql);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    
    /**
     * 获得指定表的数据行数
     * 
     * @param tableName 表格�
     * @return 数据的行�
     */
    public int getRowCount(String tableName)
    {
        int count = 0;
        Cursor cur = null;
        try
        {
            StringBuilder builder = new StringBuilder("select count(1) from ");
            builder.append(tableName).append(";");
            cur = query(builder.toString());
            if (cur.moveToFirst())
            {
                //TODO：这种实现方法对吗？�
                count = cur.getInt(0);
            }
        }
        finally
        {
            if (cur != null)
            {
                cur.close();
            }
        }

        return count;
    }
    

    /**
     * 判断表在数据库中是否存在
     * 
     * @param tableName 表格�
     * @return  表格是否存在
     */
    public boolean exist(String tableName)
    {
        return DBManager.existTable(getDBHelper(), tableName);
    }
    
    public int getVersion()
    {
        return getDBHelper().getVersion();
    }
    
    /**
     * 从数据库中删除表
     * 
     * @param tableName     表格�
     */
    public void deleteTable(String tableName)
    {
        getDBHelper().execSQL("DROP TABLE " + tableName + ";");
    }
    

    /**
     * 清空表内所有数�
     * 
     * @param tableName 表格�
     */
    public void clearTable(String tableName)
    {
        getDBHelper().execSQL("DELETE FROM " + tableName + ";");

        fireDataChangedListener(tableName, DATA_DEL);
    }
    
    /**
     * 清空表内符合条件的数据�
     * <功能详细描述>
     * @param tableName 表格名字
     * @param selection 约束条件 
     */
    public void clearTable(String tableName, String selection)
    {
        if (StringUtils.isEmpty(selection))
        {
            clearTable(tableName);
        }
        else
        {
            String sql = "DELETE FROM " + tableName + " WHERE " + selection + ";";
            getDBHelper().execSQL(sql);
            fireDataChangedListener(tableName, DATA_DEL);
        }
    }
    
    /**
     * 清空表内符合多个条件的数据�?<功能详细描述>
     * 
     * @param tableName 表格名字
     * @param whereClause 约束条件
     * @param whereArgs 相关字段
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public int clearTable(String tableName, String whereClause, String[] whereArgs)
    {
        try
        {
            return getDBHelper().delete(tableName, whereClause, whereArgs);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return -1;
    }
    
    /**
     * 向表中插入一条数�
     * 
     * @param tableName 表格�
     * @param values    数据
     * @return 如果成功返回数据所在的行数，否则返�?1.
     */
    public int insert(String tableName, ContentValues values)
    {
        int id = (int) getDBHelper().insert(tableName, "Null", values);
        if (id != -1)
        {
            fireDataChangedListener(tableName, DATA_ADD);
        }
        return id;
    }

    /**
     * 替换一条数�
     * 
     * @param tableName     表格�
     * @param values        this map contains the initial column values for the row.   
     */
    public void replace(String tableName, ContentValues values)
    {
        getDBHelper().replace(tableName, "Null", values);
        fireDataChangedListener(tableName, DATA_CHANGED);
    }
    
    /**
     * 执行sql查询
     * 
     * @param sql       查询语句
     * @return 查询结果
     */
    public Cursor query(String sql)
    {
        return getDBHelper().rawQuery(sql, null);
    }
    
    /**
     * 数据库查�
     * 
     * @param distinct          是否消除查询结果中的重复值�
     * @param table             表格�
     * @param selection         查询条件
     * @param groupBy           结合合计函数，根据一个或多个列对结果集进行分�
     * @param having            对分组结果进行过�?           
     * @param orderBy           指定排序方式
     * @param limit             限制条件            
     * @return 查询结果
     */
    public Cursor query(boolean distinct, String table, String selection, String groupBy, String having,
            String orderBy, String limit)
    {
        return getDBHelper().query(distinct, table, new String[] {"*"}, selection, null, groupBy, having, orderBy,
                limit);
    }
    
    

    /**
     * 查询数据
     * 
     * @param table     表格�
     * @param orderBy   顺序
     * @return 按序排列好的查询结果�
     */
    public Cursor listAll(String table, String orderBy)
    {
        return query(false, table, null, null, null, orderBy, null);
    }
    

    /**
     * 查询数据
     * <功能详细描述>
     * @param sql               sql语句
     * @param selectionArgs     查询条件
     * @return 查询结果
     */
    public Cursor rawQuery(String sql, String[] selectionArgs)
    {
        return getDBHelper().rawQuery(sql, selectionArgs);
    }
    

    /**
     * 数据库查�
     * 
     * @param table         表格�
     * @param selection     查询条件
     * @return 查询结果
     */
    public Cursor query(String table, String selection)
    {
        return query(false, table, selection, null, null, null, null);
    }
    

    /**
     * 数据库查�
     * 
     * @param table             表格�
     * @param selection         查询条件
     * @param orderBy           排序方式
     * @return  查询结果
     * @throws Exception
     */
    public Cursor query(String table, String selection, String orderBy)
    {
        return query(false, table, selection, null, null, orderBy, null);
    }
    

    /**
     * 数据库查�
     * <功能详细描述>
     * @param table             表格�
     * @param columns           被查询的属性域
     * @param selection         查询条件
     * @param selectionArgs     查询条件参数
     * @param orderBy           排序方式
     * @return 查询结果
     */
    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String orderBy)
    {
        return getDBHelper().query(table, columns, selection, selectionArgs, null, null, orderBy);
    }
    

    /**
     * 删除指定条件的数�
     * 
     * @param tableName 表格�
     * @param whereClause 条件
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause
     */
    public int delete(String tableName, String whereClause)
    {
        return delete(tableName, whereClause, null);
    }

    /**
     * 删除指定条件的数�?<功能详细描述>
     * 
     * @param tableName 表格�
     * @param whereClause 条件
     * @param whereArgs 条件变量
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public int delete(String tableName, String whereClause, String[] whereArgs)
    {
        int rowCount = getDBHelper().delete(tableName, whereClause, whereArgs);
        fireDataChangedListener(tableName, DATA_DEL);

        return rowCount;
    }
    

    /**
     * 更新数据
     * 
     * @param table         表格�
     * @param values        更新后的数据
     * @param whereClause   更新条件
     * @return the number of rows affected 
     */
    public int update(String table, ContentValues values, String whereClause)
    {
        return update(table, values, whereClause, null);
    }
    

    /**
     * 更新数据
     * <功能详细描述>
     * @param table         表格�
     * @param values        更新后的数据
     * @param whereClause   更新条件
     * @param whereArgs     条件参数
     * @return the number of rows affected
     */
    public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
    {
        int rowCount =  -1;
        try
        {
            rowCount = getDBHelper().update(table, values, whereClause, whereArgs);
            fireDataChangedListener(table, DATA_CHANGED);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

        return rowCount;
    }
    

    /**
     * 初始�
     * 设置上下�
     * @param context  上下�
     */
    public void init(Context context)
    {
        this.mContext = context;
    }
    
    /**
     * Check whether specified table existed in the database
     * 
     * @param db 数据库对�
     * @param tableName 指定表名�
     * @return 表格是否存在
     */
    public static boolean existTable(SQLiteDatabase db, String tableName)
    {
        boolean result = false;
        StringBuilder builder = new StringBuilder("select 1 from sqlite_master where type='table' and name='");
        builder.append(tableName).append("';");
        Cursor cur = null;
        try
        {
            cur = db.rawQuery(builder.toString(), null);
            if (cur.moveToNext())
            {
                result = true;
            }
            
        }
        finally
        {
            if (cur != null)
            {
                cur.close();
            }
        }
        return result;
    }

    /**
     * 
     * 升级数据�
     * <功能详细描述>
     * @param db            数据库句�
     * @param oldVersion    老数据库的版本号
     * @param newVersion    新数据库的版本号
     */
    public void upgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Logger.d(TAG, "upgrade, oldVersion = " + oldVersion + ", newVersion = " + newVersion);
        
        Iterator<Entry<String, TableManager>> entrySetIterator = tableManagers.entrySet().iterator();
        while (entrySetIterator.hasNext())
        {
            Entry<String, TableManager> entry = entrySetIterator.next();
            // Logger.d(TAG, "entry.getKey = " + entry.getKey());
            entry.getValue().upgradeTable(db, oldVersion, newVersion);
        }
    }


    /**
     * 
     * 创建数据�
     * <功能详细描述>
     * @param db    数据库句�
     */
    public void create(SQLiteDatabase db)
    {
        Logger.d(TAG, "create");
        Iterator<Entry<String, TableManager>> entrySetIterator = tableManagers.entrySet().iterator();
        while (entrySetIterator.hasNext())
        {
            Entry<String, TableManager> entry = entrySetIterator.next();
            entry.getValue().createTable(db);
        }
    }
    
    /**
     * 
     * 控制数据库的创建和升级�
     * A helper class to manage database creation and version management.
     */
    private class OpenHelper extends SQLiteOpenHelper
    {
        public OpenHelper(Context context, String name, CursorFactory factory, int version)
        {
            super(context, name, factory, version);
            Logger.d(TAG, "OpenHelper");
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            Logger.d(TAG, "OpenHelper, onCreate");
            DBManager.this.create(db);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Logger.d(TAG, "OpenHelper, onUpgrade");
            DBManager.this.mOldVersion = oldVersion;
            DBManager.this.mNewVersion = newVersion;

            DBManager.this.upgrade(db, oldVersion, newVersion);
        }
    }
    
    /**
     * 获取表格管理
     * @param name 表格
     * @return 表格管理
     */
    public TableManager getTableManager(String name)
    {
        return tableManagers.get(name);
    }

	@Override
	public byte managerId() 
	{
		return DB_ID;
	}
}

